﻿CREATE PROCEDURE [acms].[Search_GetAllByTopicAndSearchTerm]
	@TopicId int,
	@SearchTerm varchar(50),
	@SiteVersion DateTime, 
	@StartIndex int,
	@MaxRecords int,
	@TotalRecordsOut int output
	
AS
	declare @tmpSiteVersion DateTime
	declare @tmp table(Id int, Rank int)
	declare @FullTextSearch varchar(50)
	set @FullTextSearch = '(FORMSOF(INFLECTIONAL,"' + @SearchTerm + '")'
	
	set @tmpSiteVersion = acms.GetPublishDate(@SiteVersion)

	insert into @tmp
	Select distinct [Key], Rank 
	from FreeTextTable ([acms].[Posts], (DisplayName, Summary), @FullTextSearch)
	where [Key] in (Select PostId From [acms].PostTopic_View Where TopicId = @TopicId)
	union
	select a.PostId, b.[Rank] 
	From [acms].[PostControl_View] a
		inner join PostPositions c on a.PostId = c.PostId and isNull(PublishDate, '9999/12/31') = isNull(@tmpSiteVersion, '9999/12/31') 
		inner join FreeTextTable
			([acms].[PostControls], Value, @FullTextSearch) b on a.Id = b.[Key]
	where
		a.PostId in (Select PostId From [acms].PostTopic_View Where TopicId = @TopicId)


	select * from
	(
		select d.*, e.ParentPath, e.PublishDate as SiteVersion, c.Rank / 10 as Rank, Row_Number() over (Order By c.Rank desc) as RowNumber
		from
		(
			select b.Id, Max(a.Rank) as Rank
			from @tmp a
				inner join [acms].[Post_View] b on a.Id = b.Id
			group by b.Id
		) as c
			inner join [acms].[Post_View] d on c.Id = d.Id
			inner join [acms].[PostPositions] e on d.Id = e.PostId and isNull(PublishDate, '9999/12/31') = isNull(@tmpSiteVersion, '9999/12/31') 
			inner join [acms].[Template_View] f on d.TemplateId = f.Id and f.ExcludeFromSearch = 0
	) e
	where e.RowNumber between @StartIndex + 1 and @StartIndex + @MaxRecords

	select @TotalRecordsOut = Count(Id) 
	From
		(
			select distinct Id
			from @tmp
		) as a
	

RETURN 0;